package com.example.exceldemo.service.impl;

import com.example.exceldemo.model.ExcelparaModel;
import com.example.exceldemo.service.ExportExcelService;
import com.example.exceldemo.utils.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Slf4j
@Service
public class ExportExcelServiceImpl implements ExportExcelService {

	/**
	 * 
	 * @description 改为通用导出Excel的方法
	 * @param <T>
	 * @param request
	 * @param response
	 * @param resultList
	 * @throws Exception
	 */
	@Override
	public <T> void exportExcel(HttpServletRequest request, HttpServletResponse response, List<T> resultList, ExcelTemplate template) {
		
		String codedFileName = template.getCodedFileName();
		String sheetName = template.getSheetName();
		List<String> tableVal = template.getTableValArr(); //对应excel表头
		List<String> tableKey = template.getTableKeyArr(); //对应table表头
		String configId = template.getConfigId();
		//step.4 进行查询返回结果
        List<Map<String, Object>> result = EntityUtils.convertListBean2ListMap(resultList);
//		codedFileName = fileNameEncoder(request,codedFileName);
		List<ExcelModel> entityList = new ArrayList<ExcelModel>();
		for(int i=0;i<tableVal.size();i++){
			entityList.add(new ExcelModel(tableKey.get(i) ,tableVal.get(i)));
		}
		//批量导出海量数据
		exportExcel(response,entityList,result,codedFileName,sheetName,configId);
	}
	
	

	/**
	 * 
	 * @param response
	 * @param entityList
	 * @param result
	 * @param codedFileName
	 * @param sheetName
	 * @throws UnsupportedEncodingException
	 */
	public void exportExcel(HttpServletResponse response, List<ExcelModel> entityList, List<Map<String, Object>> result, String codedFileName, String sheetName, String configId){

        Workbook workbook = null;
        String suffix = "xls";
        FileOutputStream fos = null;
        try {
        	if("cir".equals(configId)){
        		workbook = templetExcel(result,configId,entityList);
        	}
        	else{
        		workbook = exportContacts(entityList,result, sheetName);
        		suffix = "xlsx";
        	}
        	
        	response.setHeader("Content-disposition", "attachment;filename=" + codedFileName + "." + suffix);
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.flushBuffer();
            OutputStream outputStream = response.getOutputStream();
            workbook.write(response.getOutputStream());
            outputStream.flush();
            outputStream.close();
        } catch (Exception e1) {
            e1.printStackTrace();
        }finally {
        	if(fos != null ) {
        		try {
					fos.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
        	}
		}
       
	}

	/**
	 * 
	 * @param entityList
	 * @param result
	 * @param sheetName
	 * @return
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 */
    public Workbook exportContacts(List<ExcelModel> entityList, List<Map<String, Object>> result, String sheetName)
            throws IllegalArgumentException, IllegalAccessException, ClassNotFoundException {
    	Workbook xssfWorkbook = null;
		try {
			xssfWorkbook = createExcelFile(entityList,result, sheetName);
		} catch (Exception e) {
			e.printStackTrace();
		}
        return xssfWorkbook;
    }

    /**
     * 
     * @param entityList
     * @param result
     * @param sheetName
     * @return
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     * @throws ClassNotFoundException
     * @throws Exception
     */
    public Workbook createExcelFile(List<ExcelModel> entityList,List<Map<String, Object>> result,String sheetName)
            throws IllegalArgumentException, IllegalAccessException, ClassNotFoundException, Exception {
    	
    	SXSSFWorkbook wb = new SXSSFWorkbook(100);			//关键语句
    	long  startTime = System.currentTimeMillis();	//开始时间
		log.info("strat execute time: " + startTime);
		//执行通用方法还是导出特定页面的模板
		generiExcel(entityList, result, sheetName, wb);
		long finishedTime = System.currentTimeMillis();	//处理完成时间
		log.info("finished execute time: " + (finishedTime - startTime) + "微秒");
		
        return wb;
    }
    
    /**
     * @decription 通用一般Excel导出方法
     * @param entityList
     * @param result
     * @param sheetName
     * @param wb
     */
    public void generiExcel(List<ExcelModel> entityList,List<Map<String, Object>> result,String sheetName,SXSSFWorkbook wb){
    	
    	Sheet sheet = null;		//工作表对象
		Row nRow = null;		//行对象
		Cell nCell = null;		//列对象
		int rowNo = 0;		//总行号
		int pageRowNo = 0;	//页行号
		
		for(int i = 0; i < result.size() + 1 ; i++){
			
			//打印300000条后切换到下个工作表，最大不超过1048576条数据
			if(rowNo%300000==0){
				log.info("Current Sheet:" + rowNo/300000);
				sheet = wb.createSheet(sheetName);//建立新的sheet对象
				sheet = wb.getSheetAt(rowNo/300000);		//动态指定当前的工作表
				pageRowNo = 0;		//每当新建了工作表就将当前工作表的行号重置为0
			}	
			rowNo++;
			nRow = sheet.createRow(pageRowNo++);	//新建行对象
			
			for(int j=0;j<entityList.size();j++){
				if(pageRowNo == 1){//填充列头
					nCell = nRow.createCell(j);
					nCell.setCellValue(entityList.get(j).getName());
				}else{//填充内容
					Map<String, Object> mapResult = result.get(i-1);
					nCell = nRow.createCell(j);
					Object key = entityList.get(j).getKey();
					String cellValue = String.valueOf(mapResult.get(key)==null?"":mapResult.get(key));
					if(POIUtils.dateformat.contains(key) && StringUtils.isNotEmpty(cellValue)){
						cellValue = DateUtils.dateformat(cellValue, DateUtils.DATETIME_YMD);
					}
					nCell.setCellValue(cellValue);
				}
			}
			if(rowNo%10000==0){
				log.info("row no: " + rowNo);
			}
		}
    }
    
	/**
	 * 
	 * @description 模板导出！
	 * @param result
	 * @param configId
	 * @param entityList
	 * @return
	 */
	public Workbook templetExcel(List<Map<String, Object>> result,String configId,List<ExcelModel> entityList){
		
		log.info("模板导出！");
		Resource resource = new ClassPathResource("templet/cir.xls");
		InputStream is = null;
		Workbook workbook = null;
		Sheet sheet = null;
		
		try {
			is = resource.getInputStream();// 将excel文件转为输入流
			workbook = new HSSFWorkbook(is);// 创建个workbook，
			// 获取第一个sheet
			sheet = workbook.getSheetAt(0);
			
			Row fromRow = sheet.getRow(1);
			int startRow = 2;//设置开始行
			ExcelparaModel excelpara = new ExcelparaModel(2,0,1,2);
			//填充内容
			int end = fill( excelpara,result, sheet, fromRow, workbook,entityList) -1;
			
			POIUtils.deleteRow(sheet,startRow);
//			sheet.setForceFormulaRecalculation(true);
			
		} catch (Exception e1) {
			log.info("模板导出:"+e1);
			e1.printStackTrace();
		}
		return workbook;
	}
	
	/**
	 * 
	 * @param result
	 * @param sheet
	 * @param fromRow
	 * @param workbook
	 * @param entityList
	 * @return
	 */
	private int fill(ExcelparaModel excelpara, List<Map<String, Object>> result, Sheet sheet, Row fromRow, Workbook workbook, List<ExcelModel> entityList){
		int startRow = excelpara.getStartRow();

		int endnum = startRow;
		Cell nCell = null;		//列对象
		for(int i = startRow; i < result.size()+startRow; i++,endnum++){
			Row toRow  = POIUtils.createRow(sheet,i);
			POIUtils.copyRow(workbook, fromRow, toRow, false);
			for(int j = 0; j < entityList.size() ; j++){
					Map<String, Object> mapResult = result.get(i-startRow);
					nCell = toRow.getCell(j + excelpara.getStartColumnHead());
					if(nCell != null && StringUtil.isNotEmpty(entityList.get(j).getKey())){
						String val = String.valueOf(mapResult.get(entityList.get(j).getKey())==null?"":mapResult.get(entityList.get(j).getKey()));
						if((StringUtil.isFloatNumeric(val) || StringUtil.isNumericString(val)) && val.indexOf("-") == -1 && val.length() < 9 ){
							if(Double.valueOf(val) == 0) {
								nCell.setCellValue("");
							}else {
								nCell.setCellValue(Double.valueOf(val));
							}
							
						}else{
							nCell.setCellValue(String.valueOf(val));
						}
					}
			}
		}
		
		return endnum;
	}
}
